package cn.com.klun.tjy.other.util;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;

public class DataConnectUtil {
    private static final Logger logger = LoggerFactory.getLogger(DataConnectUtil.class);

    // 获取数据库连接
    private static Connection getConn(String DATABASE_DRIVER, String DATABASE_URL, String DATABASE_ACCOUNT, String DATABASE_PASSWORD) {
        // java sql connection 数据库连接
        Connection conn = null;
        try {
            Class.forName(DATABASE_DRIVER);// 全类名获取类的实例 反射获取 （这个自动注册到DirverManager中）
            // com.mysql.jdbc.Driver;
            // dataBaseName = DATABASE_URL
            conn = DriverManager.getConnection(DATABASE_URL, DATABASE_ACCOUNT, DATABASE_PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 用sql操作数据库 -批量插入-股票基本信息
     * @param DATABASE_DRIVER 驱动 com.mysql.jdbc.Driver;
     * @param DATABASE_NAME  数据库名称
     * @param DATABASE_ACCOUNT 账号
     * @param DATABASE_PASSWORD 密码
     * @param list 数据集
     * @return
     */
    public static boolean batchInsert(String DATABASE_DRIVER, String DATABASE_NAME, String DATABASE_ACCOUNT, String DATABASE_PASSWORD, List<List<String>> list){
        PreparedStatement preparedStatement = null;// 声明、叙述、报表、清单
        Connection connection = null; // 数据库连接
        String sql = "";
        try {
            if(StringUtils.isEmpty(DATABASE_DRIVER)){
                DATABASE_DRIVER = "com.mysql.jdbc.Driver";
            }
            int listSize = list.size();
            connection = getConn( DATABASE_DRIVER, DATABASE_NAME, DATABASE_ACCOUNT, DATABASE_PASSWORD);
            sql = "insert into tbl_all_stock_info(stock_code,EX,name,industry) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            for (List<String> info :list) {
                preparedStatement.setString(1,info.get(0));
                preparedStatement.setString(2,info.get(1));
                preparedStatement.setString(3,info.get(2));
                preparedStatement.setString(4,info.get(3));
                preparedStatement.addBatch();
            }
            int[] ints = preparedStatement.executeBatch();// 执行这条sql
            int l = ints.length;
            int insetSize = 0;
            for (int i = 0; i < l; i++) {
                insetSize += ints[i];
            }
            if (insetSize == listSize){
                return true;
            }
        } catch (Exception e) {
            logger.debug("调用数据库连接失败");
        } finally {
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                logger.debug("调用数据库关闭连接失败");
            }
        }
        return false;
    }

    /**
     * 用sql操作数据库 -批量插入 日K
     * @param DATABASE_DRIVER 驱动 com.mysql.jdbc.Driver;
     * @param DATABASE_NAME  数据库名称
     * @param DATABASE_ACCOUNT 账号
     * @param DATABASE_PASSWORD 密码
     * @param list 数据集
     * @return
     */
    public static boolean batchInsertDayK(String DATABASE_DRIVER, String DATABASE_NAME, String DATABASE_ACCOUNT, String DATABASE_PASSWORD, List<List<String>> list){
        PreparedStatement preparedStatement = null;// 声明、叙述、报表、清单
        Connection connection = null; // 数据库连接
        String sql = "";
        try {
            if(StringUtils.isEmpty(DATABASE_DRIVER)){
                DATABASE_DRIVER = "com.mysql.jdbc.Driver";
            }
            int listSize = list.size();
            connection = getConn( DATABASE_DRIVER, DATABASE_NAME, DATABASE_ACCOUNT, DATABASE_PASSWORD);
            sql = "insert into tbl_stock_dayk_info(sto_id,trn_date,star_pri,max_pri,min_pri,end_pri,trn_num,trn_amount) values(?,?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            int len = 20180101;
            for (List<String> info :list) {
                preparedStatement.setString(1,"sh600001");
                preparedStatement.setString(2,String.valueOf(len));
                preparedStatement.setString(3,info.get(1));
                preparedStatement.setString(4,info.get(2));
                preparedStatement.setString(5,info.get(3));
                preparedStatement.setString(6,info.get(4));
                preparedStatement.setString(7,info.get(5));
                preparedStatement.setString(8,info.get(6));
                preparedStatement.addBatch();
                len++;
            }
            int[] ints = preparedStatement.executeBatch();// 执行这条sql
            int l = ints.length;
            int insetSize = 0;
            for (int i = 0; i < l; i++) {
                insetSize += ints[i];
            }
            if (insetSize == listSize){
                return true;
            }
        } catch (Exception e) {
            logger.debug("调用数据库连接失败");
        } finally {
            try {
                if (preparedStatement != null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                logger.debug("调用数据库关闭连接失败");
            }
        }
        return false;
    }
}
